/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tourmsystem.report.controller;

/**
 * @author Lam
 */
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import java.awt.Component;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import tourmsystem.tourmanaging.classes.Definition;
import tourmsystem.tourmanaging.classes.Tour;
//import tourmsystem.report.view.TotalTurnover;
public class ReportControler {
    String Usename = "root";
    String Password = "";
    String DataBase = "tourmanagerment";
    
    private Connection connect=null;     
    private Statement statement=null;    
    private ResultSet result =null; 
     
    public int sumcus,tong, month;
    public float sumturnover,turnover,turnover_all;
    public float sumprofit,profit;
    public float sumcost,cost;
    public int sumfinal;
    public Date date;
    public String s;
    public float sumturnover_all;
    public float sumTurnover_month;
    private Component c;
    private float sum;
    public ReportControler(){     
        try { 
            Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
        }  
        catch (Exception e) { 
            JOptionPane.showMessageDialog(c,"Không có driver kết nối","Lỗi",JOptionPane.ERROR_MESSAGE); 
        }  
    }
//==========================================================================================
  /*phuong thuc  ket noi co so du lieu*/
    public void getConnect() throws Exception{ 
               String url = "jdbc:mysql://127.0.0.1:3306/" + this.DataBase;
        if (connect == null) {
            try {
                Class.forName("org.gjt.mm.mysql.Driver");
                this.connect = DriverManager.getConnection(url, Usename, Password);
           // statement=connect.createStatement();
            } catch (java.lang.ClassNotFoundException e) {
                throw new Exception(e.getMessage());
            }
        }
        if(connect!=null){
            
        }
    } 
//==========================================================================================
    protected ResultSet getStament(){ //getstatement
        try { 
            if(this.statement==null || this.statement.isClosed()){ 
                this.statement = connect.createStatement();   
                String query="select * from tour";
                this.result=this.statement.executeQuery(query);
            } 
        } catch (SQLException e) { 
                e.getMessage();
        } 
            return result;
    } 
//==========================================================================================
    public ResultSet excuteQuery(String Query) { 
        try{ 
            getConnect(); 
            getStament(); 
            this.result = statement .executeQuery(Query); 
        } 
        catch(Exception e){ 
            JOptionPane.showMessageDialog(c,"Không thực hiện được lệnh","Lỗi",JOptionPane.ERROR_MESSAGE); 
            e.getStackTrace();
        } 
        return this.result; 
    }
//=========================================================================================
    public void executeUpdate(String Query) throws SQLException{ 
        try{ 
            getConnect(); 
            getStament(); 
            statement.executeUpdate(Query); 
        } 
         catch(SQLException e){ 
             JOptionPane.showMessageDialog(c,"Không thực hiện được lệnh","Lỗi" ,JOptionPane.ERROR_MESSAGE); 
             e.getStackTrace();
         } 
        catch(Exception ie){
        }
        //JOptionPane.showConfirmDialog(null,"Update successful");
         //this.Close(); 
    } 
    
//============================================================================================
    /*phuong thuc dong ket noi*/
    public void Close() throws SQLException{ 

        if(result!=null){ 
            this.result.close(); 
            this.result = null; 
        } 
            this.statement.close(); 
            this.statement = null; 

            this.connect.close(); 
            this.connect =null; 
    }
 //============================================================================================
 /*phuong thuc getYear*/
      public String  getYear(){
        String selectQuery="select startdate from tour";
        if (connect != null){
        try{
        Statement st = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
        ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(selectQuery);
        while(rs.next()){
             s=new SimpleDateFormat("yyyy").format(rs.getDate("startdate"));
        }
        st.close();
        }catch(SQLException e){
            JOptionPane.showConfirmDialog(null, "loi khong thuc hien duoc"+e);
        }
        }
        return s;
    }
//============================================================================================
 /*Lay tour theo thang va insert vao bang report*/
    public float sumTurnoverByMonth(int month,String id){
         String selectQuery="select id,idtour,quantity,price from tour,custour where tour.id=custour.idtour";
         ArrayList<Tour> tours = new ArrayList<Tour>();
        if (connect != null){
        try{
        Statement st = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
        ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(selectQuery);
        
        while(rs.next()){
            Tour t = new Tour();
             Calendar cal = Calendar.getInstance();
            cal.setTime(t.getStartDate());
            int tMonth = cal.get(Calendar.MONTH)  ;
            if(tMonth == month){
         try {
                t=findTourById(id);
                sum+=rs.getFloat("price")*rs.getInt("quantity")-((t.getDiscount()/100)*t.getPrice()*rs.getInt("quantity"));
         } catch (Exception ex) {
                 Logger.getLogger(ReportControler.class.getName()).log(Level.SEVERE, null, ex);
           }
          tours.add(t);
            }
        }
        st.close();
        return sum;
        }catch(SQLException e){
            JOptionPane.showConfirmDialog(null, "loi khong thuc hien duoc"+e);
        }
        }
        return 0;
    }
//=============================================================================================
/*phuong thuc tim tour theo id, tra lai doi tuong tour neu tin thay, tra lai null neu khong*/
    public Tour findTourById(String idString) throws Exception {
        getConnect();
        String sql = "SELECT `Id`, `name`, `startDate`, `duration`, `type`, `schedule`, `price`, "
                + "`discount`, `maxPeople`, `imgLinks` FROM `tour` WHERE id = '" + idString + "'";
        Statement st = (Statement) connect.createStatement();
        ResultSet rs = null;
        rs = st.executeQuery(sql);
        if (rs.next()) {
            System.out.println("co ket qua");
            Tour t = getTourFromResultSet(rs);
            return t;
        } else {
            System.out.println("khong co ket qua");
            return null;
        }
    }
//===============================================================================================
     private Tour getTourFromResultSet(ResultSet rs) throws Exception {
        if (rs == null) {
            return null;
        }
        Tour tour = new Tour();
        tour.setId(rs.getString(Definition.T_ID));
        tour.setName(rs.getString(Definition.T_NAME));
        tour.setDiscount(rs.getFloat(Definition.T_DISC));
        tour.setPrice(rs.getFloat(Definition.T_PRICE));
        tour.setImgLink(rs.getString(Definition.T_IMGL));
        tour.setMaxP(rs.getInt(Definition.T_MAXP));
        tour.setDuration(rs.getInt(Definition.T_DISC));
        tour.setSchedule(rs.getString(Definition.T_SCHEL));
        java.util.Date ud = new java.util.Date(rs.getDate(Definition.T_DATE).getTime());
        tour.setStartDate(ud);
        tour.setType(rs.getInt(Definition.T_TYPE));

        return tour;
    }
  //============================================================================================ 
    //TINH DOANH THU CUA TOUR THEO ID
    public float sumTurnover(String id)
    {
         sumturnover=0;
        String selectQuery = "SELECT id,idtour,quantity,price from tour,custour where tour.id=custour.idtour and tour.id="+id+"";
        if (connect != null){
        try{
        Statement st = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
        ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(selectQuery);
        
        while(rs.next()){
            turnover=rs.getInt("quantity")*rs.getFloat("price");
            sumturnover+=turnover;
        }
        st.close();
        }catch(SQLException e){
             JOptionPane.showConfirmDialog(null, "Error!");
        }
        }
        return sumturnover;
    }

//============================================================================================
    //TINH TONG CHI PHI
        public float sumCostbyMonth(String id, int month){
             sumcost=0;
        String selectQuery = "SELECT cost,id,idtour from custour,tour where tour.id=custour.idtour ";
         ArrayList<Tour> tours = new ArrayList<Tour>();
        if (connect != null){
        try{
        Statement st = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
        ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(selectQuery);
        while(rs.next()){
             Tour t = new Tour();
             Calendar cal = Calendar.getInstance();
            cal.setTime(t.getStartDate());
            int tMonth = cal.get(Calendar.MONTH)  ;
            if(tMonth==month){
            cost=rs.getFloat("cost");
            sumcost+=cost;
            }
             tours.add(t);
        }
       
        st.close();
        }catch(SQLException e){
              JOptionPane.showConfirmDialog(null, "Error!");
              e.getStackTrace();
        }
        }
        return sumcost;
        }
 //==============================================================================================
    //TINH LOI NHUAN
        public float sumProfitByMonth(String id,int month){
            profit+=this.sumTurnoverByMonth(month,id)-this.sumCostbyMonth(id,month);
            return profit;
        }
 //==============================================================================================
   //inser du lieu vao bang report
        public void insertIntoReport(int month,String id){
           
             try{
            try {
                this.getConnect();
            } catch (Exception ex) {
                Logger.getLogger(ReportControler.class.getName()).log(Level.SEVERE, null, ex);
            }
        Statement st = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
        ResultSet.CONCUR_READ_ONLY);
            Tour t;
            try {
                t = findTourById(id);
                st.execute("INSERT INTO report (`idReport`, `idtour`, `month`, `name`, `type`, `price`, `discount`, `turnover`, `profit`)"
                 + " VALUES ('', '"+t.getId()+"', '"+month+"', '"+t.getName()+"', '"+t.getType()+"', '"+t.getPrice()+"',"
                + " '"+t.getDiscount()+"', '"+this.sumTurnoverByMonth(month, id) +"', '"+this.sumProfitByMonth(id, month) +"')");
            } catch (Exception ex) {
                Logger.getLogger(ReportControler.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }catch(SQLException e){
             JOptionPane.showConfirmDialog(null, "Error!");
        }
             
        }
 //============================================================================================
        public static  void main(String args[]) throws Exception
      {
         ReportControler r= new ReportControler();
         r.getConnect();
      }
} 
